
const { validateFields } = require('../public/util.js')
const { successMsg, errorMsg,page } = require('../public/constant.js')
const {executeTransaction}=require('../mysql/public.js')
const pool = require('../mysql/mysql.js');
const express = require('express');
const router = express.Router();
const multer = require('multer');
const upload = multer();

const tableName='room'


// 添加
router.post('/Add', upload.none(), validateFields([
  { field: 'roomNumber', noEmpty: true, validType: 'string' },
  { field: 'roomTypeId', noEmpty: true, validType: 'number' },
  { field: 'roomStateId', noEmpty: true, validType: 'number' },
]), (req, res) => {
  const { roomNumber,roomTypeId,roomStateId } = req.body;
  executeTransaction({pool,res,queries:[
    {
      sql:`SELECT * FROM ${tableName} WHERE room_number=? and is_delete=0`,
      params:[roomNumber],
      stopFlag:(result)=>result.length,
      stopEvent:()=>{res.status(200).send(errorMsg('房间号重复'))},
      successEvent:()=>{}
    },
    {
      sql:`INSERT INTO ${tableName} (room_number,room_type_id,room_state_id) VALUES (?, ?,?)`,
      params:[roomNumber,roomTypeId,roomStateId],
      successEvent:()=>{res.status(200).send(successMsg())}
    }
  ]})

});
// 修改
router.post('/Edit', upload.none(), validateFields([
  { field: 'roomId', noEmpty: true, validType: 'number' },
  { field: 'roomNumber', noEmpty: true, validType: 'string' },
  { field: 'roomTypeId', noEmpty: true, validType: 'number' },
  { field: 'roomStateId', noEmpty: true, validType: 'number' },
]), (req, res) => {
  const { roomId,roomTypeId,roomStateId,roomNumber } = req.body;
  executeTransaction({pool,res,queries:[
    {
      sql:`SELECT * FROM ${tableName} WHERE room_id=? and is_delete=0`,
      params:[roomId],
      stopFlag:(result)=>!result.length,
      stopEvent:()=>{res.status(200).send(errorMsg('没有该房间'))},
      successEvent:()=>{},
    },
    {
      sql:`SELECT * FROM ${tableName} WHERE room_number=? and room_id not in (?)`,
      params:[roomNumber,roomId],
      stopFlag:(result)=>!!result.length,
      stopEvent:()=>{res.status(200).send(errorMsg('房间号重复'))},
      successEvent:()=>{},
    },
    {
      sql:`UPDATE ${tableName} SET room_type_id=?,room_state_id=? WHERE room_id=?`,
      params:[roomTypeId,roomStateId,roomId],
      stopFlag:(result)=>!!result.length,
      stopEvent:()=>{res.status(200).send(errorMsg('房间号重复'))},
      successEvent:()=>{res.status(200).send(successMsg())},
    },
  ]})
});

const getField=(tableName,fieldList)=>{
  var field=''
  for(var i in fieldList){
    field+=`${tableName}.${fieldList[i]},`
  }
  field=field.slice(0,-1)
  return field
}
let roomTypeFieldList=['room_type_name','room_type_price','bed_num','description']
var roomTypeField=getField('room_type',roomTypeFieldList)
let roomStateFieldList=['room_state_name']
var roomStateField=getField('room_state',roomStateFieldList)
let commonSql=`SELECT ${tableName}.*,${roomTypeField},${roomStateField} FROM ${tableName} 
left join room_type on ${tableName}.room_type_id=room_type.room_type_id 
left join room_state on ${tableName}.room_state_id=room_state.room_state_id`
// 查询数据
router.get('/List', (req, res) => {
  let {roomStateId,roomTypeId}=req.query
  let conditionSql=''
  const arr=[]
  if(roomStateId){
    conditionSql+=` and ${tableName}.room_state_id=?`
    arr.push(roomStateId)
  }
  if(roomTypeId){
    conditionSql+=` and ${tableName}.room_type_id=?`
    arr.push(roomTypeId)
  }
  pool.query(`${commonSql} where ${tableName}.is_delete=0${conditionSql}`,arr, (err, result) => {
    if (err){
      console.log(err)
      return res.status(500).send(errorMsg('数据库错误'));
    }
    var r = [...result]
    res.status(200).send(successMsg(r));
  });
});
// 查询数据
router.post('/PageList',upload.none(), (req, res) => {
  let {pageSize,pageIndex,roomStateId,roomTypeId}=req.body
  pageSize=pageSize||10
  pageIndex=pageIndex||1
  let conditionSql=''
  const arr=[pageSize,(pageIndex-1)*pageSize]
  const conditionArr=[]
  if(roomStateId){
    conditionSql+=` and ${tableName}.room_state_id=?`
    arr.unshift(roomStateId)
    arr.push(roomStateId)
  }
  if(roomTypeId){
    conditionSql+=` and ${tableName}.room_type_id=?`
    conditionArr.push(roomTypeId)
    arr.push(roomTypeId)
  }
  const sql=`${commonSql} 
  WHERE ${tableName}.is_delete = 0${conditionSql} LIMIT ? OFFSET ?; 
  SELECT COUNT(*) FROM ${tableName} WHERE is_delete = 0${conditionSql}`
  pool.query(sql,[...conditionArr,...arr],(err, result) => {
    if (err){
      console.log(err)
      return res.status(500).send(errorMsg('数据库错误'));
    }
    res.status(200).send(successMsg(page({data:result[0],total:result[1][0]['COUNT(*)'],pageIndex})));
  });
});
// 获取某一个
router.post('/GetOne', upload.none(), validateFields([
  { field: 'roomId', noEmpty: true, validType: 'number' },
]), (req, res) => {
  const { roomId } = req.body
  const sql = `${commonSql} WHERE ${tableName}.room_id=? and ${tableName}.is_delete=0`
  pool.query(sql, [roomId], (err, result) => {
    if (err){
      return res.status(500).send(errorMsg('数据库错误'));
    }
    var r = [...result]
    res.status(200).send(successMsg(r))
  })
});
// 删除
router.get('/Delete', (req, res) => {
  const { roomId } = req.query
  if (!roomId) {
      return res.status(200).send(errorMsg('缺少参数roomId'));
  }
  executeTransaction({pool,res,queries:[
    {
      sql:`SELECT * FROM ${tableName} WHERE room_id=?`,
      params:[roomId],
      stopFlag:(result)=>!result.length,
      stopEvent:()=>{res.status(200).send(errorMsg('无效id'))},
      successEvent:()=>{}
    },
    {
      sql:`UPDATE ${tableName} SET is_delete=1 WHERE room_id=?`,
      params:[roomId],
      stopFlag:false,
      successEvent:()=>{res.status(200).send(successMsg('操作成功'))}
    }
  ]})
});
module.exports = router;